﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DAL.Entity;
using DAL.Models;

namespace DAL.Persistencia
{
    public class FileCarrinhoDAL
    {
        private NewTierraEntities Con;

        public FileCarrinhoDAL()
        {
            Con = new NewTierraEntities();
        }

        public object MontaCarrinhoHoteis(int IdQuotGrupo)
        {
            try
            {
                return (from fc in Con.File_Carrinho
                        join ft in Con.File_Tarifas on fc.File_id equals ft.File_id
                        where fc.Quotation_Grupo_Id == IdQuotGrupo &&
                              ft.Range_id == null
                        orderby ft.Data_From, ft.Ordem
                        select new
                        {
                            ft.Ordem,
                            ft.S_meal_nome,
                            ft.S_meal_status,
                            NomeSupplier = ft.S_nome,
                            Categoria = ft.Categoria,
                            Room = ft.Room,
                            QtdTarifa = ft.Qtd_Tarifas,
                            ValorTarifa = ft.S_merc_tarif_valor,
                            Mod = ft.Moeda,
                            Markup = ft.markup,
                            Desconto = ft.desconto,
                            MarkupNet = ft.markupNet,
                            DescontoNet = ft.descontoNet,
                            ft.File_Tarifas_id,
                            ft.Paying_Pax,
                            ft.NumNoites,
                            DataFrom = ft.Data_From,
                            DataTo = ft.Data_To,
                            ft.Status,
                            ft.NomePacote
                        }).ToList();
            }
            catch
            {
                throw;
            }
        }

        public object MontaCarrinhoHoteisNew(int IdQuotGrupo)
        {
            try
            {
                return (from fc in Con.File_Carrinho
                        join ft in Con.File_Tarifas on fc.File_id equals ft.File_id
                        where fc.Quotation_Grupo_Id == IdQuotGrupo &&
                              ft.Range_id != null
                        orderby ft.Data_From, ft.Ordem
                        select new
                        {
                            ft.Ordem,
                            ft.S_meal_nome,
                            ft.S_meal_status,
                            NomeSupplier = ft.S_nome,
                            Categoria = ft.Categoria,
                            Room = ft.Room,
                            QtdTarifa = ft.Qtd_Tarifas,
                            ValorTarifa = ft.S_merc_tarif_valor,
                            Mod = ft.Moeda,
                            Markup = ft.markup,
                            Desconto = ft.desconto,
                            MarkupNet = ft.markupNet,
                            DescontoNet = ft.descontoNet,
                            ft.File_Tarifas_id,
                            ft.Paying_Pax,
                            ft.NumNoites,
                            DataFrom = ft.Data_From,
                            DataTo = ft.Data_To,
                            ft.Status,
                            ft.NomePacote
                        }).ToList();
            }
            catch
            {
                throw;
            }
        }

        public object MontaCarrinhoHoteisNewRange(int IdQuotGrupo)
        {
            try
            {


                return (from fc in Con.File_Carrinho
                        join ft in Con.File_Tarifas on fc.File_id equals ft.File_id
                        join rg in Con.Ranges on ft.File_Tarifas_id equals rg.FileTabelaId
                        where fc.Quotation_Grupo_Id == IdQuotGrupo &&
                              ft.Range_id != null
                        orderby ft.Data_From, ft.Ordem
                        select new
                        {
                            ft.Ordem,
                            ft.S_meal_nome,
                            ft.S_meal_status,
                            NomeSupplier = ft.S_nome,
                            Categoria = ft.Categoria,
                            Room = ft.Room,
                            QtdTarifa = ft.Qtd_Tarifas,
                            ValorTarifa = ft.S_merc_tarif_valor,
                            Mod = ft.Moeda,
                            Markup = ft.markup,
                            Desconto = ft.desconto,
                            MarkupNet = ft.markupNet,
                            DescontoNet = ft.descontoNet,
                            ft.File_Tarifas_id,
                            ft.Paying_Pax,
                            ft.NumNoites,
                            DataFrom = ft.Data_From,
                            DataTo = ft.Data_To,
                            ft.Status,
                            ft.NomePacote,
                            rg.Ranges_id
                        }).ToList();
            }
            catch
            {
                throw;
            }
        }

        public object MontaCarrinhoTransfers(int IdQuotGrupo)
        {
            try
            {
                return (from fc in Con.File_Carrinho
                        join ft in Con.File_Transfers on fc.File_id equals ft.File_id
                        where fc.Quotation_Grupo_Id == IdQuotGrupo &&
                              ft.Range_id == null
                        orderby ft.Data_From, ft.Ordem
                        select new
                        {
                            ft.Ordem,
                            NomeSupplier = ft.S_nome,
                            TituloTransf = ft.Transf_nome,
                            QtdTarifa = ft.Qtd_Transf,
                            ValorTarifa = ft.Transf_valor,
                            Mod = ft.Moeda,
                            Markup = ft.markup,
                            Desconto = ft.desconto,
                            MarkupNet = ft.markupNet,
                            DescontoNet = ft.descontoNet,
                            ft.File_Transf_id,
                            ft.Paying_Pax,
                            DataFrom = ft.Data_From,
                            DataTo = ft.Data_To,
                            ft.Trf_Tours,
                            ft.Status,
                            ft.SubServico,
                            ft.S_id
                        }).ToList();
            }
            catch
            {
                throw;
            }
        }

        public object MontaCarrinhoExtras(int IdQuotGrupo)
        {
            try
            {
                return (from fc in Con.File_Carrinho
                        join ft in Con.File_ServExtra on fc.File_id equals ft.File_id
                        where fc.Quotation_Grupo_Id == IdQuotGrupo &&
                              ft.Range_id == null
                        orderby ft.Data_From, ft.Ordem
                        select new
                        {
                            ft.Ordem,
                            NomeSupplier = ft.S_nome,
                            ft.File_ServExtra_nome,
                            ft.Qtd_ServExtr,
                            ft.File_ServExtra_id,
                            ft.Paying_Pax,
                            ValorTarifa = ft.File_ServExtra_valor,
                            Markup = ft.markup,
                            Desconto = ft.desconto,
                            MarkupNet = ft.markupNet,
                            DescontoNet = ft.descontoNet,
                            DataFrom = ft.Data_From,
                            DataTo = ft.Data_To,
                            Mod = ft.Moeda,
                            ft.Status
                        }).ToList();
            }
            catch
            {
                throw;
            }
        }

        public object MontaCarrinhoNewFile(int IdQuotGrupo, int OptQuote)
        {
            try
            {
                return ((from fcr in Con.File_Carrinho
                         join fts in Con.File_Tarifas on fcr.File_id equals fts.File_id
                         join rgn in Con.Ranges on fts.File_Tarifas_id equals rgn.FileTabelaId
                         where fcr.Quotation_Grupo_Id == IdQuotGrupo &&
                               fts.Range_id != null &&
                               rgn.Flag.Equals("hotel") &&
                               rgn.OptQuote == OptQuote
                         select new
                         {
                             IdRetorna = fts.File_Tarifas_id,
                             dtFrom = fts.Data_From,
                             dtTo = fts.Data_To,
                             Nome = fts.S_nome,
                             NomeTr = fts.S_nome,
                             Room = fts.Room,
                             Categoria = fts.Categoria,
                             Flag = rgn.Flag,
                             MealNome = fts.S_meal_nome,
                             MealStatus = fts.S_meal_status,
                             Status = fts.Status,
                             DAY = rgn.DAY,
                             Bulk = fts.TBulk,
                             SubServico = false,
                             Paying_Pax = fts.Paying_Pax,
                             Ordem = fts.Ordem
                         }).Distinct().ToList())
                        .Union
                        ((from fcr in Con.File_Carrinho
                          join fts in Con.File_Transfers on fcr.File_id equals fts.File_id
                          join rgn in Con.Ranges on fts.File_Transf_id equals rgn.FileTabelaId
                          where fcr.Quotation_Grupo_Id == IdQuotGrupo &&
                                fts.Range_id != null &&
                               rgn.Flag.Equals("servico") &&
                               rgn.OptQuote == OptQuote                          
                          select new
                          {
                              IdRetorna = fts.File_Transf_id,
                              dtFrom = fts.Data_From,
                              dtTo = fts.Data_To,
                              Nome = fts.S_nome,
                              NomeTr = fts.Transf_nome,
                              Room = "",
                              Categoria = "",
                              Flag = rgn.Flag,
                              MealNome = "",
                              MealStatus = "",
                              Status = fts.Status,
                              DAY = rgn.DAY,
                              Bulk = fts.TBulk,
                              SubServico = fts.SubServico == true ? true : false,
                              Paying_Pax = fts.Paying_Pax,
                              Ordem = fts.Ordem
                          }).Distinct().ToList())
                          .Union
                          ((from fcr in Con.File_Carrinho
                            join fse in Con.File_ServExtra on fcr.File_id equals fse.File_id
                            join rgn in Con.Ranges on fse.File_ServExtra_id equals rgn.FileTabelaId
                            where fcr.Quotation_Grupo_Id == IdQuotGrupo &&
                                  fse.Range_id != null &&
                               rgn.Flag.Equals("extra") &&
                               rgn.OptQuote == OptQuote                            
                            select new
                            {
                                IdRetorna = fse.File_ServExtra_id,
                                dtFrom = fse.Data_From,
                                dtTo = fse.Data_To,
                                Nome = fse.File_ServExtra_nome,
                                NomeTr = "",
                                Room = "",
                                Categoria = "",
                                Flag = rgn.Flag,
                                MealNome = "",
                                MealStatus = "",
                                Status = fse.Status,
                                DAY = rgn.DAY,
                                Bulk = fse.TBulk,
                                SubServico = false,
                                Paying_Pax = fse.Paying_Pax,
                                Ordem = fse.Ordem
                            }).Distinct().ToList()).OrderBy(a => a.Ordem).ThenBy(a => a.dtFrom).ThenBy(a => a.dtTo);

            }
            catch
            {
                throw;
            }
        }

        public object MontaCarrinhoNewFile(int IdQuotGrupo)
        {
            try
            {                

                return ((from fcr in Con.File_Carrinho
                         join fts in Con.File_Tarifas on fcr.File_id equals fts.File_id
                         join rgn in Con.Ranges on fts.File_Tarifas_id equals rgn.FileTabelaId
                         where fcr.Quotation_Grupo_Id == IdQuotGrupo &&
                               fts.Range_id != null &&
                               rgn.Flag.Equals("hotel")
                         //orderby fts.Data_From, fts.Ordem
                         select new
                         {
                             IdRetorna = fts.File_Tarifas_id,
                             dtFrom = fts.Data_From,
                             dtTo = fts.Data_To,
                             Nome = fts.S_nome,
                             NomeTr = fts.S_nome,
                             Room = fts.Room,
                             Categoria = fts.Categoria,
                             Flag = rgn.Flag,
                             MealNome = fts.S_meal_nome,
                             MealStatus = fts.S_meal_status,
                             Status = fts.Status,
                             DAY = rgn.DAY,
                             Bulk = fts.TBulk,
                             SubServico = false,
                             Paying_Pax = fts.Paying_Pax
                         }).Distinct().ToList())
                        .Union
                        ((from fcr in Con.File_Carrinho
                          join fts in Con.File_Transfers on fcr.File_id equals fts.File_id
                          join rgn in Con.Ranges on fts.File_Transf_id equals rgn.FileTabelaId
                          where fcr.Quotation_Grupo_Id == IdQuotGrupo &&
                                fts.Range_id != null &&
                               rgn.Flag.Equals("servico")
                          //orderby fts.Data_From, fts.Ordem
                          select new
                          {
                              IdRetorna = fts.File_Transf_id,
                              dtFrom = fts.Data_From,
                              dtTo = fts.Data_To,
                              Nome = fts.S_nome,
                              NomeTr = fts.Transf_nome,
                              Room = "",
                              Categoria = "",
                              Flag = rgn.Flag,
                              MealNome = "",
                              MealStatus = "",
                              Status = fts.Status,
                              DAY = rgn.DAY,
                              Bulk = fts.TBulk,                              
                              SubServico = fts.SubServico == true ? true : false,
                              Paying_Pax = fts.Paying_Pax
                          }).Distinct().ToList())
                          .Union
                          ((from fcr in Con.File_Carrinho
                            join fse in Con.File_ServExtra on fcr.File_id equals fse.File_id
                            join rgn in Con.Ranges on fse.File_ServExtra_id equals rgn.FileTabelaId
                            where fcr.Quotation_Grupo_Id == IdQuotGrupo &&
                                  fse.Range_id != null &&
                               rgn.Flag.Equals("extra")
                            //orderby fse.Data_From, fse.Ordem
                            select new
                            {
                              IdRetorna = fse.File_ServExtra_id,
                              dtFrom = fse.Data_From,
                              dtTo = fse.Data_To,
                              Nome = fse.File_ServExtra_nome,
                              NomeTr = "",
                              Room = "",
                              Categoria = "",
                              Flag = rgn.Flag,
                              MealNome = "",
                              MealStatus = "",
                              Status = fse.Status,
                              DAY = rgn.DAY,
                              Bulk = fse.TBulk,
                              SubServico = false,
                              Paying_Pax = fse.Paying_Pax
                          }).Distinct().ToList()).OrderBy(a => a.dtFrom).ThenBy(a => a.dtTo);
               
            }
            catch
            {
                throw;
            }
        }

        public object MontaCarrinhoNewFileFinal(int IdFile)
        {
            try
            {

                return ((from fcr in Con.File_Carrinho
                         join fts in Con.Quote_Tarifas on fcr.File_id equals fts.File_id                        
                         where fcr.File_id == IdFile &&
                               fts.TC == false                                         
                         select new
                         {
                             IdRetorna = fts.Quote_Tarifas_id,
                             dtFrom = fts.Data_From,
                             dtTo = fts.Data_To,
                             Nome = fts.S_nome,
                             NomeTr = fts.S_nome,
                             Room = fts.Room,
                             Categoria = fts.Categoria,                             
                             MealNome = fts.S_meal_nome,
                             MealStatus = fts.S_meal_status,
                             Status = fts.Status,                             
                             Bulk = fts.TBulk,
                             Flag = fts.Flag,
                             SubServico = false,
                             Paying_Pax = fts.Paying_Pax
                         }).Distinct().ToList())
                        .Union
                        ((from fcr in Con.File_Carrinho
                          join fts in Con.Quote_Transf on fcr.File_id equals fts.File_id                          
                          where fcr.File_id == IdFile  &&
                                fts.TC == false                                    
                          select new
                          {
                              IdRetorna = fts.Quote_Transf_id,
                              dtFrom = fts.Data_From,
                              dtTo = fts.Data_To,
                              Nome = fts.S_nome,
                              NomeTr = fts.Transf_nome,
                              Room = "",
                              Categoria = "",
                              MealNome = "",
                              MealStatus = "",
                              Status = fts.Status,
                              Bulk = fts.TBulk,
                              Flag = fts.Flag,
                              SubServico = fts.SubServico == true ? true : false,
                              Paying_Pax = fts.Paying_Pax
                          }).Distinct().ToList())
                          .Union
                          ((from fcr in Con.File_Carrinho
                            join fse in Con.Quote_ServExtra on fcr.File_id equals fse.File_id                            
                            where fcr.File_id == IdFile &&
                                  fse.TC == false                                                             
                            select new
                            {
                                IdRetorna = fse.Quote_ServExtra_id,
                                dtFrom = fse.Data_From,
                                dtTo = fse.Data_To,
                                Nome = fse.File_ServExtra_nome,
                                NomeTr = "",
                                Room = "",
                                Categoria = "",                                
                                MealNome = "",
                                MealStatus = "",
                                Status = fse.Status,                                
                                Bulk = fse.TBulk,
                                Flag = fse.Flag,
                                SubServico = false,
                                Paying_Pax = fse.Paying_Pax                               
                            }).Distinct().ToList()).OrderBy(a => a.dtFrom).ThenBy(a => a.dtTo);

            }
            catch
            {
                throw;
            }
        }

        public object MontaCarrinhoHoteisBases(int IdFile, string BaseDe, string BaseAte)
        {
            try
            {

                return (from fc in Con.File_Carrinho
                        join ft in Con.File_Tarifas on fc.File_id equals ft.File_id
                        join rg in Con.Ranges on ft.Range_id equals rg.Ranges_id
                        where fc.File_id == IdFile &&
                              ft.Range_id != null &&
                              rg.Ranges_de.Equals(BaseDe) &&
                              rg.Ranges_ate.Equals(BaseAte)
                        orderby ft.Data_From, ft.Ordem
                        select new
                        {
                            ft.S_meal_nome,
                            ft.S_meal_status,
                            NomeSupplier = ft.S_nome,
                            Categoria = ft.Categoria,
                            Room = ft.Room,
                            QtdTarifa = ft.Qtd_Tarifas,
                            ValorTarifa = ft.S_merc_tarif_valor,
                            Mod = ft.Moeda,
                            Markup = ft.markup,
                            Desconto = ft.desconto,
                            MarkupNet = ft.markupNet,
                            DescontoNet = ft.descontoNet,
                            ft.File_Tarifas_id,
                            ft.Paying_Pax,
                            ft.NumNoites,
                            DataFrom = ft.Data_From,
                            DataTo = ft.Data_To,
                            ft.Status,
                            ft.NomePacote,
                            rg.Ranges_de,
                            rg.Ranges_ate,
                            ft.S_merc_tarif_venda,
                            ft.S_merc_tarif_total
                        }).ToList();
            }
            catch
            {
                throw;
            }
        }

        public int RetornaFileID(int IdQuotGrupo)
        {
            try
            {
                return Con.File_Carrinho.Where(f => f.Quotation_Grupo_Id == IdQuotGrupo).SingleOrDefault().File_id;
            }
            catch
            {
                throw;
            }
        }

        public void Salvar(File_Carrinho f)
        {
            try
            {
                Con.AddToFile_Carrinho(f);
                Con.SaveChanges();
            }
            catch
            {
                throw;
            }
        }

        public File_Carrinho ObterPorIdGrupo(int IdQuotGrupo)
        {
            try
            {
                return Con.File_Carrinho.Where(f => f.Quotation_Grupo_Id == IdQuotGrupo).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public int RetornaFile(string Quote)
        {
            try
            {

                return (from qot in Con.Quotation
                        join qgr in Con.Quotation_Grupo on qot.Quotation_Id equals qgr.Quotation_Id
                        join fca in Con.File_Carrinho on qgr.Quotation_Grupo_Id equals fca.Quotation_Grupo_Id
                        where qot.Quotation_Code.Equals(Quote)
                        select fca).SingleOrDefault().File_id;

            }
            catch
            {
                throw;
            }
        }

        public File_Carrinho RetornaFileObj(string Quote)
        {
            try
            {

                return (from qot in Con.Quotation
                        join qgr in Con.Quotation_Grupo on qot.Quotation_Id equals qgr.Quotation_Id
                        join fca in Con.File_Carrinho on qgr.Quotation_Grupo_Id equals fca.Quotation_Grupo_Id
                        where qot.Quotation_Code.Equals(Quote)
                        select fca).SingleOrDefault();

            }
            catch
            {
                throw;
            }
        }

        public int RetornaQuote(int IdFile)
        {
            try
            {

                return (from qot in Con.Quotation
                        join qgr in Con.Quotation_Grupo on qot.Quotation_Id equals qgr.Quotation_Id
                        join fca in Con.File_Carrinho on qgr.Quotation_Grupo_Id equals fca.Quotation_Grupo_Id
                        where fca.File_id == IdFile
                        select qot).SingleOrDefault().Quotation_Id;

            }
            catch
            {
                throw;
            }
        }

        public Quotation RetornaQuoteObj(string QuotationCode)
        {
            try
            {

                return Con.Quotation.Where(q => q.Quotation_Code.Equals(QuotationCode)).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public bool VerificaExisteFile(int IdFile)
        {
            try
            {

                return ((from qse in Con.Quote_ServExtra
                        where qse.File_id == IdFile
                        select new
                        {
                            ID = qse.Quote_ServExtra_id
                        }).Union(
                        from qts in Con.Quote_Tarifas
                        where qts.File_id == IdFile
                        select new
                        {
                            ID = qts.Quote_Tarifas_id
                        }).Union(
                        from qtr in Con.Quote_Transf
                        where qtr.File_id == IdFile
                        select new
                        {
                            ID = qtr.Quote_Transf_id
                        })).Count() != 0;

            }
            catch 
            {                
                throw;
            }
        }

        public bool VerificaExisteTCQuotes(int IdFile)
        {
            try
            {

                return ((from qse in Con.Quote_ServExtra
                         where qse.TC == true &&
                               qse.File_id == IdFile
                         select new
                         {
                             ID = qse.Quote_ServExtra_id
                         }).Union(
                        from qts in Con.Quote_Tarifas
                        where qts.TC == true &&
                              qts.File_id == IdFile
                        select new
                        {
                            ID = qts.Quote_Tarifas_id
                        }).Union(
                        from qtr in Con.Quote_Transf
                        where qtr.TC == true &&
                              qtr.File_id == IdFile
                        select new
                        {
                            ID = qtr.Quote_Transf_id
                        })).Count() != 0;

            }
            catch
            {
                throw;
            }
        }

        public List<TotalFileModel> ValoresTotaisFile(int IdFile)
        {
            SqlCon cn = new SqlCon();
            try
            {

                string sql = "SELECT Transf_total * Paying_Pax  AS Total, Flag, '' AS Room ";
                sql = sql + "FROM Quote_Transf WHERE (File_id = @p1) UNION ALL ";
                sql = sql + "SELECT S_merc_tarif_total * Paying_Pax AS Total, Flag, Room ";
                sql = sql + "FROM Quote_Tarifas WHERE (File_id = @p1) UNION ALL ";
                sql = sql + "SELECT File_ServExtra_total * Paying_Pax  AS Total, Flag, '' AS Room ";
                sql = sql + "FROM Quote_ServExtra WHERE (File_id = @p1) ";

                cn.AbrirConexao();
                cn.Cmd = new SqlCommand(sql, cn.Con);
                cn.Cmd.Parameters.AddWithValue("@p1", IdFile);

                cn.Dr = cn.Cmd.ExecuteReader();

                List<TotalFileModel> lista = new List<TotalFileModel>();

                while (cn.Dr.Read())
                {
                    TotalFileModel tfm = new TotalFileModel();

                    tfm.total = Convert.ToDecimal(cn.Dr["Total"]);
                    tfm.room = cn.Dr["Room"].ToString();
                    tfm.flag = cn.Dr["Flag"].ToString();

                    lista.Add(tfm);
                }

                return lista;
            }
            catch
            {
                throw;
            }
            finally
            {
                cn.FecharConexao();
            }
        }

    }
}
